/*
 * @(#)EmailMessageSenderComparator.java
 *
 * Copyright (c) 2004 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */

package com.dcivision.upgrade.handler;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.dcivision.framework.Utility;
import com.dcivision.upgrade.UpgradeContext;

/**
 * @author      Brook.Huang
 * @company     DCIVision Limited
 * @creation    19/04/2007
 * @version     $Revision: 1.2 $
 */
public class UpgradeHandler4FormModule319To320 extends UpgradeHandlerBase {

  public UpgradeHandler4FormModule319To320(UpgradeContext upgradeContext) throws Exception {
    super(upgradeContext);
  }

  protected void upgradeBegin() throws Exception {
    upgradeContext.logReport("<p><b>START UPGRADE PARADM FORM MODULE</b></p>\n", null);
    // Add your code here to deal with things in the front of this module's
    // upgrade.
  }

  protected void upgradeEnd() throws Exception {

    // (1) Update FORM_ELEMENT.
    // Alter function for FREEZED and UNIQUENESS see SQLScript.
    // ~~~~~~~~~~~~~~~~~~~~~~~~

    // (2) Alter function for FORM_SUBMISSION.
    // Generate WORKFLOW_RECORD_ID and TRACK_ID.
    // ~~~~~~~~~~~~~~~~~~~~~~~~
    generateWorkflowRecordIDAndTrackID();

    // (3) Alter function for FORM_DATA_ELEMENT.
    // Generate SUBMIT_FLAG.
    // ~~~~~~~~~~~~~~~~~~~~~~~~
    generateSubmitFlag();

    // (4) Alter function for MTM_WF_PROGRESS_ACCESS_FORM.
    // Insert record for every access between progress and form submission.
    // This section have been moved to UpgradeHandler4WorkflowModule319To320.
    // ~~~~~~~~~~~~~~~~~~~~~~~~

  }

  ///////////////////////////////////////////////////////////////////////////
  ///
  /// DB Migration.
  ///
  ///////////////////////////////////////////////////////////////////////////

  // ~ Major Part to Migration List ------------------------------------------

  // Generate workflow record id and tarck id for FORM_SUBMISSION.
  private int generateWorkflowRecordIDAndTrackID() throws Exception {

    upgradeContext.logReport("<p><font color=blue>START UPGRADE FORM_SUBMISSION</font><br>\n", null);

    int count = 0;

    Statement progressSubmissionStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet progressSubmissionRs = null;

    progressSubmissionRs = progressSubmissionStat.executeQuery("SELECT A.OBJECT_ID, A.TRACK_ID, C.WORKFLOW_RECORD_ID "
        + "FROM MTM_WF_PROGRESS_SYSTEM_OBJECT A LEFT JOIN WORKFLOW_PROGRESS B ON B.ID = A.WORKFLOW_PROGRESS_ID "
        + "LEFT JOIN WORKFLOW_STEP C ON C.ID = B.WORKFLOW_STEP_ID " + "WHERE A.BEAN_CLASS_NAME = 'com.dcivision.form.bean.FormSubmission'");

    while (!Utility.isEmpty(progressSubmissionRs) && progressSubmissionRs.next()) {
      count += updateFormSubmissionWorkflowRecordIDAndTrackID(progressSubmissionRs.getInt("OBJECT_ID"), progressSubmissionRs
          .getInt("WORKFLOW_RECORD_ID"), progressSubmissionRs.getInt("TRACK_ID"));
    }

    try {
      progressSubmissionRs.close();
      progressSubmissionStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport("<font color=blue>UPGRADE FORM_SUBMISSION SUCCESS (TOTAL UPDATE COUNT: " + count + ")</font><br></p>\n", null);

    return count;
  }

  // Generate submit flag for FORM_DATA_ELEMENT.
  private int generateSubmitFlag() throws Exception {

    upgradeContext.logReport("<p><font color=blue>START UPGRADE FORM_DATA_ELEMENT</font><br>\n", null);

    int count = 0;

    Statement submissionStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet submissionRs = null;

    // Update the form data element not bind with workflow first.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    submissionRs = submissionStat
        .executeQuery("SELECT ID, STATUS "
            + "FROM FORM_SUBMISSION WHERE ID NOT IN (SELECT OBJECT_ID FROM MTM_WF_PROGRESS_SYSTEM_OBJECT WHERE BEAN_CLASS_NAME = 'com.dcivision.form.bean.FormSubmission')");
    while (!Utility.isEmpty(submissionRs) && submissionRs.next()) {

      if ("S".equals(submissionRs.getString("STATUS"))) {
        count += updateFormDataElementSubmitFlagBySubmissionIDRecordStatus(submissionRs.getInt("ID"), "D", "A");
        count += updateFormDataElementSubmitFlagBySubmissionIDRecordStatus(submissionRs.getInt("ID"), "D", "I");
      }

      if ("C".equals(submissionRs.getString("STATUS"))) {
        count += updateFormDataElementSubmitFlagBySubmissionIDRecordStatus(submissionRs.getInt("ID"), "S", "A");
        count += updateFormDataElementSubmitFlagBySubmissionIDRecordStatus(submissionRs.getInt("ID"), "D", "I");
      }
    }

    // Update the form data element bind with workflow.
    // One progress just related to one form data element in SUBMIT_FLAG =
    // 'S'.
    // ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    // Update the last submit of form data element bind form first.
    submissionRs = submissionStat.executeQuery("SELECT MAX(A.SEQ) SEQ, B.STATUS "
        + "FROM FORM_DATA_ELEMENT A LEFT JOIN WORKFLOW_PROGRESS B ON B.ID = A.WORKFLOW_PROGRESS_ID "
        + "WHERE A.WORKFLOW_PROGRESS_ID IS NOT NULL " + "GROUP BY A.WORKFLOW_PROGRESS_ID, B.STATUS");
    while (!Utility.isEmpty(submissionRs) && submissionRs.next()) {
      if ("P".equals(submissionRs.getString("STATUS"))) {
        count += updateFormDataElementSubmitFlagBySeq(submissionRs.getInt("SEQ"), "D");
      } else {
        count += updateFormDataElementSubmitFlagBySeq(submissionRs.getInt("SEQ"), "S");
      }
    }

    // Update the leave form data element's submit flag bind with workflow
    // with value 'D'.
    count += updateFormDataElementSubmitFlagBindWorkflowWithDefaultValue("D");

    try {
      submissionRs.close();
      submissionStat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    upgradeContext.logReport("<font color=blue>UPGRADE FORM_DATA_ELEMENT SUCCESS (TOTAL UPDATE COUNT: " + count + ")</font><br></p>\n",
        null);

    return count;
  }

  // ~ Detail Operation ------------------------------------------------------

  private int updateFormSubmissionWorkflowRecordIDAndTrackID(int ID, int WORKFLOW_RECORD_ID, int TRACK_ID) throws Exception {

    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, WORKFLOW_RECORD_ID, TRACK_ID \n");
    sqlStat.append("  FROM FORM_SUBMISSION \n");
    sqlStat.append("  WHERE ID = ? ");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);
    stat.setInt(1, ID);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "WORKFLOW_RECORD_ID:    TRACK_ID:    ");
      upgradeContext.logReport(null, "from    " + rs.getInt("WORKFLOW_RECORD_ID") + ":    " + rs.getInt("TRACK_ID") + ":    ");
      rs.updateInt("WORKFLOW_RECORD_ID", WORKFLOW_RECORD_ID);
      rs.updateInt("TRACK_ID", TRACK_ID);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getInt("WORKFLOW_RECORD_ID") + ":    " + rs.getInt("TRACK_ID") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return count;
  }

  private int updateFormDataElementSubmitFlagBySubmissionIDRecordStatus(int SUBMISSION_ID, String SUBMIT_FLAG, String RECORD_STATUS)
      throws Exception {

    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, SUBMIT_FLAG \n");
    sqlStat.append("  FROM FORM_DATA_ELEMENT \n");
    sqlStat.append("  WHERE SUBMISSION_ID = ? \n");
    sqlStat.append("  AND RECORD_STATUS = ? \n");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);
    stat.setInt(1, SUBMISSION_ID);
    stat.setString(2, RECORD_STATUS);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "SUBMIT_FLAG:    ");
      upgradeContext.logReport(null, "from    " + rs.getString("SUBMIT_FLAG") + ":    ");
      rs.updateString("SUBMIT_FLAG", SUBMIT_FLAG);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getString("SUBMIT_FLAG") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return count;
  }

  private int updateFormDataElementSubmitFlagBySeq(int SEQ, String SUBMIT_FLAG) throws Exception {

    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, SUBMIT_FLAG \n");
    sqlStat.append("  FROM FORM_DATA_ELEMENT \n");
    sqlStat.append("  WHERE SEQ = ?");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);
    stat.setInt(1, SEQ);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "SUBMIT_FLAG:    ");
      upgradeContext.logReport(null, "from    " + rs.getString("SUBMIT_FLAG") + ":    ");
      rs.updateString("SUBMIT_FLAG", SUBMIT_FLAG);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getString("SUBMIT_FLAG") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return count;
  }

  /*
   * This should called after generate the last submit flag of form data
   * element bind with workflow.
   */
  private int updateFormDataElementSubmitFlagBindWorkflowWithDefaultValue(String SUBMIT_FLAG) throws Exception {

    int count = 0;

    PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    sqlStat.append("SELECT ID, SUBMIT_FLAG \n");
    sqlStat.append("  FROM FORM_DATA_ELEMENT \n");
    sqlStat.append("  WHERE WORKFLOW_PROGRESS_ID IS NOT NULL \n");
    sqlStat.append("  AND SUBMIT_FLAG IS NULL \n");

    stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString(), ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);

    rs = stat.executeQuery();
    while (!Utility.isEmpty(rs) && rs.next()) {
      count++;
      upgradeContext.logReport(null, "Update(ID:    " + rs.getInt("ID") + ")    ");
      upgradeContext.logReport(null, "SUBMIT_FLAG:    ");
      upgradeContext.logReport(null, "from    " + rs.getString("SUBMIT_FLAG") + ":    ");
      rs.updateString("SUBMIT_FLAG", SUBMIT_FLAG);
      rs.updateRow();
      upgradeContext.logReport(null, "to    " + rs.getString("SUBMIT_FLAG") + ":    <br>\n");
    }

    try {
      rs.close();
      stat.close();
    } catch (Exception ignore) {
      // Ignore.
    }

    return count;
  }

}
